﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using MockPrjTeam4.Entity;
using MockPrjTeam4.Common;

namespace MockPrjTeam4.DAO
{
    public class AddressDAO
    {
        public static DataTable Search(string postcode, string street, string town)
        {
            string query = string.Format("SELECT AddressId, AddressLine1, PostCode, " +
                "TownName, CountyName, CountryName " +
                "FROM Addresses, Towns, Counties, Countries " +
                "WHERE Addresses.TownId = Towns.TownId AND Towns.CountyId = Counties.CountyId " +
                "AND Counties.CountryId = Countries.CountryId ");
            if (postcode == "" && street == "" && town == "")
            {
                return SqlHelper.ExecuteQuery(query);
            }
            else
            {
                query = string.Format("SELECT AddressId, AddressLine1, PostCode, " +
                "TownName, CountyName, CountryName, " +
                "FROM Addresses, Towns, Counties, Countries " +
                "WHERE Addresses.TownId = Towns.TownId AND Towns.CountyId = Counties.CountyId " +
                "AND Counties.CountryId = Countries.CountryId " +
                "AND PostCode LIKE '%'+@Postcode+'%' " +
                "AND TownName LIKE '%'+@TownName+'%' ");
                var parameter = new Collection<SqlParameter>
                {
                    new SqlParameter("@Postcode", postcode),
                    new SqlParameter("@TownName", town)
                };
                return SqlHelper.ExecuteQuery(query, parameter);
            }
        }

         public static DataTable SearchById(int id)
         {
             string query = string.Format("SELECT AddressId, AddressLine1, AddressLine2, " +
                "AddressLine3, PostCode, " +
                "Addresses.TownId AS TownId, TownName, Towns.CountyId AS CountyId, CountyName, " +
                "Counties.CountryId, CountryName " +
                "FROM Addresses, Towns, Counties, Countries " +
                "WHERE Addresses.TownId = Towns.TownId AND Towns.CountyId = Counties.CountyId " +
                "AND Counties.CountryId = Countries.CountryId " +
                "AND Addresses.AddressId=@AddId");
             var parameter = new Collection<SqlParameter>
                {
                    new SqlParameter("@AddId", id)
                };
             return SqlHelper.ExecuteQuery(query, parameter);
         }
    }
}